﻿ALTER PROCEDURE dbo.RepSvedSemR3 
	(
		@D_S datetime,	
		@D_PO datetime,
		@K_POLGOD int
	)
AS
-- Отчет "Сведения о семьях" 
-- Раздел 2
BEGIN
	DECLARE @ludiKpo int
	DECLARE @ludiUks int
	DECLARE @detyKpo int
	DECLARE @detyUks int
		
	-- Рассчет данных по количеству КПО, уникальных клиентов в косультациях на УКС
	set @ludiKpo = (SELECT Count(K_KPO) FROM b_kpo WHERE D_OBRASCH Between @D_S And @D_PO AND F_KONS = 1)
	set @ludiUks = (SELECT count(distinct(K_KLIENT)) FROM b_uks_kons WHERE D_KONS Between @D_S And @D_PO)
	
	set @detyKpo = (SELECT Count(K_KPO) FROM b_kpo WHERE D_OBRASCH Between @D_S And @D_PO AND F_KONS = 1 
		And GOD_R >= (Year(getdate()) -18))
		
	set @detyUks = (SELECT count(distinct(b_klient.K_KLIENT)) FROM b_uks_kons 
					INNER JOIN b_klient ON b_uks_kons.K_KLIENT = b_klient.K_KLIENT
					WHERE D_KONS Between @D_S And @D_PO AND b_klient.GOD_R >= (Year(getdate()) -18))

	-- Рассчет людей в патродажах кол-ва патронажей
	DECLARE @ludiPatr int
	DECLARE @patr int
	
	set @ludiPatr = (SELECT COUNT(K_SOSTAV_SEMYI) FROM b_sostav_semyi_uks 
					INNER JOIN b_uks_patr On b_sostav_semyi_uks.K_UKS = b_uks_patr.K_UKS 
					WHERE isnull(D_NACH,D_OKON) >= @D_S AND (isnull(D_OKON,D_NACH)<= @D_PO))
	
	set @patr = (SELECT count(K_UKS) FROM b_uks_patr 
				WHERE isnull(D_NACH,D_OKON) >= @D_S AND (isnull(D_OKON,D_NACH)<= @D_PO))
	
	
	-- Вставка данных раздела 1 
	INSERT INTO bo_polgod_soc_pom (K_POLGOD, N_STROCA, N_SOC_POM, C_LUDI, C_LUDI_DETI, N_PRIM) 
	VALUES(@K_POLGOD, 1, 'Консультационно-информационные услуги, в том числе по телефону (зарегистрированные)',
			@ludiKpo + @ludiUks, @detyKpo + @detyUks,  
			'Человек '+ cast(@ludiKpo as varchar) + ' (КПО) ' + cast(@ludiUks as varchar) + ' (УКС),' +
			' из них детей ' + cast(@detyKpo as varchar)+ ' (КПО) ' + cast(@detyUks as varchar) + ' (УКС)')

	-- Вставка данных раздела 2 
	INSERT INTO bo_polgod_soc_pom (K_POLGOD, N_STROCA, N_SOC_POM, C_LUDI, C_LUDI_DETI, N_PRIM) 
	VALUES(@K_POLGOD, 2, 'Поставлено на патронаж',
			@ludiPatr, 0,'Поставлено ' + cast(@patr as varchar) + ' УКС')

RETURN
END


go

ALTER TRIGGER TrigerInsertKpo 
ON dbo.b_kpo 
FOR INSERT
AS
-- Тригер увеличивающий автоматически NPP_KPO при встакве 
begin
	DECLARE @NPP_KPO int
	DECLARE @K_KPO int
	
	set @NPP_KPO = (Select NPP_KPO from inserted)
	set @K_KPO = (Select K_KPO from inserted)
	
	if @NPP_KPO is null or @NPP_KPO = 0
	BEGIN
		set @NPP_KPO  = isnull((select max(NPP_KPO) + 1 from b_kpo where  YEAR(D_OBRASCH) = YEAR(GETDATE())),1)
		UPDATE b_kpo SET NPP_KPO = @NPP_KPO where b_kpo.K_KPO = @K_KPO
	END
end

go

ALTER TRIGGER TrigerInsertZhurnalObrasch
ON dbo.b_zhurnal_obrasch 
FOR INSERT
AS
-- Тригер увеличивающий автоматически C_NPP при встакве 
begin

	DECLARE @C_NPP int
	DECLARE @K_ZHURNAL_OBRASCH int
	
	set @C_NPP = (Select C_NPP from inserted)
	set @K_ZHURNAL_OBRASCH = (Select K_ZHURNAL_OBRASCH from inserted)
	
	if @C_NPP is null or @C_NPP = 0
	BEGIN
		set @C_NPP  = isnull((select max(C_NPP) + 1 from b_zhurnal_obrasch where  YEAR(D_OBRASCH) = YEAR(GETDATE())),1)
		UPDATE b_zhurnal_obrasch SET C_NPP = @C_NPP where K_ZHURNAL_OBRASCH = @K_ZHURNAL_OBRASCH
	END
end

go

CREATE TRIGGER TrigerInsertUks 
ON dbo.b_uks 
FOR INSERT
AS
-- Тригер увеличивающий автоматически NPP_UKS при встакве 
begin
	DECLARE @NPP_UKS int
	DECLARE @K_UKS int
	
	set @NPP_UKS = (Select NPP_UKS from inserted)
	set @K_UKS = (Select K_UKS from inserted)
	
	if @NPP_UKS is null or @NPP_UKS = 0
	BEGIN
		set @NPP_UKS  = isnull((select max(NPP_UKS) + 1 from b_uks where  YEAR(D_DZ) = YEAR(GETDATE())),1)
		UPDATE b_uks SET NPP_UKS = @NPP_UKS where b_uks.K_UKS = @K_UKS
	END
end

